import psycopg2
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker 


def select_table(cargs,query):
    con = psycopg2.connect(**cargs)
    cur = con.cursor()
    cur.execute(query)
    data = cur.fetchall()
    con.commit()
    cur.close()
    con.close()
    return data


def get_proginces(cargs):
    query = """SELECT distinct qhmc FROM dt_sy;"""
    data = select_table(cargs,query)
    return [i for i, in data]


def get_sentinelinfo(cargs,tiles,sdate,edate,cloud):
    query = f"""SELECT distinct tile,cloud,product_date,zip_path
        FROM sentinel2_l1c
        where tile in {tuple(tiles)}
        and product_date between '{sdate}'
        and '{edate}'
        and cloud < {cloud}
        and zip_path is not null;
        """
    data = select_table(cargs,query)
    return data


def select(cargs,query):
    #connect to PostgreSQL
    url=f"postgresql://{cargs['user']}:{cargs['password']}@{cargs['host']}:{cargs['port']}/{cargs['database']}"
    engine = create_engine(url)
    Session = sessionmaker(bind=engine)
    session = Session()
    df = pd.read_sql(query, engine)
    session.close()
    return df


def pgsql_2_info(cargs,tiles,sdate,edate,cloud):
    query = f"""SELECT distinct name,tile,cloud,CAST(product_date AS date) as date,zip_path
        FROM sentinel2_l1c
        where tile in {tuple(tiles)}
        and product_date between '{sdate}'
        and '{edate}'
        and cloud < {cloud}
        and zip_path is not null;
        """
    df = select(cargs,query)
    return df


def province_2_tiles(cargs,province):
    query = f"""SELECT distinct china_tile.tile
        FROM china_tile
        JOIN dt_sy 
        on st_intersects(china_tile.geom,dt_sy.shape)
        where dt_sy.qhmc = '{province}';
        """
    data = select_table(cargs,query)
    return [i for i, in data]